﻿Imports MySql.Data.MySqlClient

Public Class add1_3

    Public SQL As MySqlConnection
    Public selectedTrainee As String
    Public selectedCourse As String

    Public trainingHour As Integer
    Public trainingMin As Integer
    Public test As String
    Public checkedApprove As Integer
    Public min As String
    Dim respone As Object
    Public time_start As String
    Public time_end As String


    Public Sub New(ByRef SQLConnection As MySqlConnection, ByRef inTrainee As String, ByRef inCourse As String)
        InitializeComponent()
        selectedTrainee = inTrainee
        selectedCourse = inCourse
        SQL = SQLConnection
    End Sub

    Private Sub Label8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub

    ' Private Sub add1_3_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    'Dim mainForm As main = New main()
    '   mainForm.Show()
    'Me.Close()
    'End Sub

    Private Sub add1_3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        CheckBox1.Checked = True
        ComboBox1.Text = ComboBox1.Items(0)
        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM Employee where emp_code =" + selectedTrainee + ";"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())


            TextBox2.Text = mySqlReader("emp_gender") + mySqlReader("emp_name") + mySqlReader("emp_surname")

        End While




        SQL.Close()

        '=======================================
        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM Course where course_id =" + selectedCourse + ";"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())


            TextBox3.Text = mySqlReader("course_name")
            DateTimePicker1.Value = mySqlReader("course_start_date")
            txt_totalhr.Text = mySqlReader("course_duration")
        End While




        SQL.Close()

        '====================================



        TextBox1.Text = selectedTrainee

        ' txt_totalhr.Text = "0"

        txt_totalhr.Text = "0"


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim nextForm As add1_2 = New add1_2(SQL, selectedTrainee)
        nextForm.Show()
        Me.Close()
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' If CSng(txt_totalhr.Text) < 0 Then
        'MsgBox("error")
        'Exit Sub
        'End If

        Dim duplicatedrow As Integer
        '-----------------------------------------------
        respone = MsgBox("Are you sure to save data", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then


            Dim mySqlCommand As New MySqlCommand
            Dim mySqlAdaptor As New MySqlDataAdapter
            Dim mySqlReader As MySqlDataReader

            '=================================================================
            'FIND SIMILAR ROWS IN DB



            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If


            '  mySqlCommand.CommandText = "INSERT INTO Training VALUES ('" + selectedTrainee + "', '" + selectedCourse + +"', '" + DateTimePicker1.Value.ToString() + "', '" + NumericUpDown1.Value.ToString() + "', '" + NumericUpDown2.Value.ToString() + "', '" + NumericUpDown3.Value.ToString() + "." + NumericUpDown4.Value.ToString() + "','" + trainingHour + "','" + CheckBox1.Checked.ToString() + "');"

            'MsgBox("INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown2.Value & "', '" & NumericUpDown3.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");")

            mySqlCommand.CommandText = "SELECT COUNT(DISTINCT emp_code) AS totalemp FROM Training where  emp_code = '" & selectedTrainee & "' and course_id = '" & selectedCourse & "' ;"
            mySqlCommand.Connection = SQL
            mySqlAdaptor.SelectCommand = mySqlCommand
            mySqlReader = mySqlCommand.ExecuteReader


            While (mySqlReader.Read())
                duplicatedrow = mySqlReader("totalemp")
            End While

            SQL.Close()



            '-----------------------------------------------


            '===================================================================
            If duplicatedrow = 0 Then



                If SQL.State = ConnectionState.Closed Then
                    SQL.Open()
                End If

                If CheckBox1.Checked = True Then
                    checkedApprove = 1
                Else
                    checkedApprove = 0
                End If

                '  mySqlCommand.CommandText = "INSERT INTO Training VALUES ('" + selectedTrainee + "', '" + selectedCourse + +"', '" + DateTimePicker1.Value.ToString() + "', '" + NumericUpDown1.Value.ToString() + "', '" + NumericUpDown2.Value.ToString() + "', '" + NumericUpDown3.Value.ToString() + "." + NumericUpDown4.Value.ToString() + "','" + trainingHour + "','" + CheckBox1.Checked.ToString() + "');"

                'MsgBox("INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & NumericUpDown1.Value & "." & NumericUpDown2.Value & "', '" & NumericUpDown3.Value & "." & NumericUpDown4.Value & "', '" & trainingHour & "'," & checkedApprove & ");")

                mySqlCommand.CommandText = "INSERT INTO training (emp_code,course_id,training_date,time_start,time_end,training_hours,training_approve,training_cost_person,training_type) VALUES ('" & selectedTrainee & "', '" & selectedCourse & "', '" & DateTimePicker1.Value.Year.ToString() & "-" & DateTimePicker1.Value.Month.ToString() & "-" & DateTimePicker1.Value.Day.ToString() & "', '" & time_start & "', '" & time_end & "', '" & txt_totalhr.Text & "'," & checkedApprove & ",'" & Textcost.Text & "','" & ComboBox1.Text & "');"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = SQL

                mySqlCommand.ExecuteNonQuery()


                SQL.Close()

                If CheckBox2.Checked Then
                    Dim nextForm As add1_1 = New add1_1(SQL)
                    nextForm.Show()
                    Me.Close()
                Else
                    Dim nextForm As main = New main()
                    nextForm.Show()
                    Me.Close()
                End If

            Else
                MsgBox("This Employee has already joined this Course")

            End If

            '-----------------------------------------------


        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim nextForm As main = New main()
        nextForm.Show()
        Me.Close()
    End Sub

    Private Sub RecalculateTime()

        'Dim date1 As TimeSpan
        'Dim sum As String
        Dim dateTime As Date
        Dim dateTime2 As Date
        'Dim datetime_sub As Date
        'Dim hours As String
        Dim full_hours As Integer = 1440
        Dim int_hours As Integer
        Dim int_min As Integer

        'Dim result_hours As Integer
        Dim result_min As String

        Dim tempmin1 As Integer
        Dim tempmin2 As Integer

        Dim tempresultmin As Integer

        'datetime_sub = #12:00:00 AM#

        'Dim test As String
        dateTime = DateTimePicker2.Value
        dateTime2 = DateTimePicker3.Value

        test = dateTime2.Subtract(dateTime).ToString
        'MsgBox(test)

        Dim test1 As String
        test1 = Mid(test, 1, 1)
        ''MsgBox(test)
        '' MsgBox(test1)

        '' MsgBox(time_start)
        ''MsgBox(time_end)

        addZero(dateTime.Hour.ToString, dateTime.Minute.ToString, dateTime2.Hour.ToString, dateTime2.Minute.ToString)




        If test1 = "-" Then

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            tempresultmin = (24 * 60) + tempresultmin

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime.Minute - dateTime2.Minute)



            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_totalhr.Text)


                If (Len(int_min.ToString) = 1) Then

                    txt_totalhr.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_totalhr.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_totalhr.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_totalhr.Text = int_hours.ToString + "." + int_min.ToString


            End If


        Else

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            ''tempresultmin = tempresultmin + 60

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime2.Minute - dateTime.Minute)

            '' If (dateTime.Minute < dateTime2.Minute) Then

            ''If result_min > 30 Then
            ''int_hours = int_hours - 1
            ''End If
            ''End If

            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_totalhr.Text)


                If (Len(int_min.ToString) = 1) Then
                    txt_totalhr.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_totalhr.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_totalhr.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_totalhr.Text = int_hours.ToString + "." + int_min.ToString


            End If


            ''txt_totalhr.Text = test

        End If

        ''txt_totalhr.Text = trainingHour
        ''Label18.Text = trainingMin


    End Sub

    Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged

    End Sub

    Private Sub NumericUpDown1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        RecalculateTime()
    End Sub

    Private Sub NumericUpDown2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        RecalculateTime()
    End Sub

    Private Sub NumericUpDown3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        RecalculateTime()
    End Sub

    Private Sub NumericUpDown4_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        RecalculateTime()
    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged

    End Sub

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
        RecalculateTime()
    End Sub

    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
        RecalculateTime()
    End Sub
    Public Sub addZero(ByVal hours1 As String, ByVal min1 As String, ByVal hours2 As String, ByVal min2 As String)


        If (Len(hours1) < 2) Then
            time_start = "0" + hours1
        Else
            time_start = hours1
        End If

        If (Len(min1) < 2) Then
            time_start = time_start + ".0" + min1
        Else
            time_start = time_start + "." + min1
        End If

        If (Len(hours2) < 2) Then
            time_end = "0" + hours2
        Else
            time_end = hours2
        End If
        If (Len(min2) < 2) Then
            time_end = time_end + ".0" + min2
        Else
            time_end = time_end + "." + min2
        End If
    End Sub


    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged

    End Sub
End Class